package com.ampaiva.db;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.ampaiva.dao.MyDroidSQLiteOpenHelper;
import com.ingeniouscamel.droid.dbutil.SQLiteDBDeploy;

public class SQLiteAdapter extends SQLiteOpenHelper {
    private static final int __DB_VERSION = 1;
    private final String DB_NAME;
    private final String DB_PATH;
    private static SQLiteAdapter mAdapter;
    private final Context mContext;
    private SQLiteDatabase mDB;
    private static String zipFile;

    /**
     * Constructor Takes and keeps a reference of the passed context in order to
     * access to the application assets and resources.
     * 
     * @param context
     */
    public static SQLiteAdapter getDBAdapterInstance(Context context) {
        String[] packages = context.getPackageName().split("\\.");
        String appName = packages[packages.length - 1].toLowerCase();
        return getDBAdapterInstance(context, appName + ".db", appName + ".zip");
    }

    /**
     * getting Instance
     * 
     * @param context
     * @return DBAdapter
     */
    private static synchronized SQLiteAdapter getDBAdapterInstance(Context context, String dbName, String zipFile) {
        if (mAdapter == null) {
            SQLiteAdapter.zipFile = zipFile;
            mAdapter = new SQLiteAdapter(context, dbName);
        }
        return mAdapter;
    }

    /**
     * Constructor Takes and keeps a reference of the passed context in order to
     * access to the application assets and resources.
     * 
     * @param context
     */
    private SQLiteAdapter(Context context, String dbName) {
        super(context, dbName, null, __DB_VERSION);
        this.mContext = context;
        this.DB_NAME = dbName;
        this.DB_PATH = "/data/data/" + mContext.getApplicationContext().getPackageName() + "/databases/" + dbName;
        // The Android's default system path of your application database is
        // "/data/data/mypackagename/databases/"
    }

    private String getDatabasePath() {
        return DB_PATH;
    }

    /**
     * Creates an empty database on the system and rewrites it with your own
     * database.
     **/
    public void createDataBase() {
        // By calling following method
        // 1) an empty database will be created into the default system path
        // of your application
        // 2) than we overwrite that database with our database.
        getReadableDatabase();
        try {
            copyDataBase();
        } catch (IOException e) {
            throw new Error("Error copying database from assets: " + e.getMessage());
        }
    }

    public void dropDatabase() {
        close();
        mContext.deleteDatabase(getDatabasePath());
    }

    /**
     * Check if the database already exist to avoid re-copying the file each
     * time you open the application.
     * 
     * @return true if it exists, false if it doesn't
     */
    public boolean databaseExists() {
        try {
            SQLiteDatabase checkDB = SQLiteDatabase.openDatabase(getDatabasePath(), null, SQLiteDatabase.OPEN_READONLY | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
            checkDB.close();
            return true;
        } catch (SQLiteException e) {
            // database does't exist yet.
        }
        return false;
    }

    /**
     * Check if the database already exist to avoid re-copying the file each
     * time you open the application.
     * 
     * @return true if it exists, false if it doesn't
     */
    public boolean exists() {
        return databaseExists();
    }

    /**
     * Copies your database from your local assets-folder to the just created
     * empty database in the system folder, from where it can be accessed and
     * handled. This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException {
        // Open your local db as the input stream
        InputStream myInput = mContext.getAssets().open(DB_NAME);
        // Path to the just created empty db
        String outFileName = getDatabasePath();
        // Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);
        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    /**
     * Open the database
     * 
     * @throws SQLException
     */
    public void open() {
        // mDB = SQLiteDatabase.openDatabase(getDatabasePath(), null,
        // SQLiteDatabase.OPEN_READWRITE | SQLiteDatabase.CREATE_IF_NECESSARY);
        mDB = getWritableDatabase();
    }

    /**
     * Close the database if exist
     */
    @Override
    public synchronized void close() {
        if (mDB != null) {
            mDB.close();
            mDB = null;
        }
        super.close();
    }

    /**
     * Call on creating data base for example for creating tables at run time
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            SQLiteDBDeploy.deploy(db, mContext, zipFile);

            // if you like to download file from remote site comment above line
            // and uncomment below line.
            // SQLiteDBDeploy.deploy(sqlLiteDb,"http://ingenious-camel.googlecode.com/svn/trunk/SQLiteDBDeployer/assets/northwind.zip");
        } catch (IOException e) {
            Log.e(MyDroidSQLiteOpenHelper.class.getSimpleName(), e.getMessage(), e);
            throw new Error(e.getMessage());
        }
    }

    /**
     * can used for drop tables then call onCreate(db) function to create tables
     * again - upgrade
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    // ----------------------- CRUD Functions ------------------------------

    /**
     * This function used to select the records from DB.
     * 
     * @param tableName
     * @param tableColumns
     * @param whereClase
     * @param whereArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @return A Cursor object, which is positioned before the first entry.
     */
    public Cursor selectRecordsFromDB(String tableName, String[] tableColumns, String whereClase, String whereArgs[], String groupBy, String having,
            String orderBy) {
        return mDB.query(tableName, tableColumns, whereClase, whereArgs, groupBy, having, orderBy);
    }

    /**
     * select records from db and return in list
     * 
     * @param tableName
     * @param tableColumns
     * @param whereClase
     * @param whereArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @return ArrayList>
     */
    public ArrayList<ArrayList<String>> selectRecordsFromDBList(String tableName, String[] tableColumns, String whereClase, String whereArgs[], String groupBy,
            String having, String orderBy) {

        ArrayList<ArrayList<String>> retList = new ArrayList<ArrayList<String>>();
        ArrayList<String> list = new ArrayList<String>();
        Cursor cursor = mDB.query(tableName, tableColumns, whereClase, whereArgs, groupBy, having, orderBy);
        if (cursor.moveToFirst()) {
            do {
                list = new ArrayList<String>();
                for (int i = 0; i < cursor.getColumnCount(); i++) {
                    list.add(cursor.getString(i));
                }
                retList.add(list);
            } while (cursor.moveToNext());
        }
        return retList;
    }

    /**
     * This function used to insert the Record in DB.
     * 
     * @param tableName
     * @param initialValues
     * @return -1 in case of failure otherwise return no of row(s) are updated
     */
    public long insertRecordsInDB(String tableName, ContentValues initialValues) {
        return mDB.insert(tableName, null, initialValues);
    }

    /**
     * This function used to update the Record in DB.
     * 
     * @param tableName
     * @param initialValues
     * @param whereClause
     * @param whereArgs
     * @return 0 in case of failure otherwise return no of row(s) are updated
     */
    public int updateRecordsInDB(String tableName, ContentValues initialValues, String whereClause, String whereArgs[]) {
        return mDB.update(tableName, initialValues, whereClause, whereArgs);
    }

    /**
     * This function used to delete the Record in DB.
     * 
     * @param tableName
     * @param whereClause
     * @param whereArgs
     * @return 0 in case of failure otherwise return no of row(s) are deleted.
     */
    public int deleteRecordInDB(String tableName, String whereClause, String[] whereArgs) {
        return mDB.delete(tableName, whereClause, whereArgs);
    }

    // --------------------- Select Raw Query Functions ---------------------

    /**
     * apply raw Query
     * 
     * @param query
     * @param selectionArgs
     * @return Cursor
     */
    public Cursor selectRecordsFromDB(String query, String[] selectionArgs) {
        return mDB.rawQuery(query, selectionArgs);
    }

    /**
     * apply raw query and return result in list
     * 
     * @param query
     * @param selectionArgs
     * @return ArrayList>
     */
    public ArrayList<ArrayList<String>> selectRecordsFromDBList(String query, String[] selectionArgs) {
        ArrayList<ArrayList<String>> retList = new ArrayList<ArrayList<String>>();
        ArrayList<String> list = new ArrayList<String>();
        Cursor cursor = mDB.rawQuery(query, selectionArgs);
        if (cursor.moveToFirst()) {
            do {
                list = new ArrayList<String>();
                for (int i = 0; i < cursor.getColumnCount(); i++) {
                    list.add(cursor.getString(i));
                }
                retList.add(list);
            } while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return retList;
    }

    public DatabaseInfo getDabaseInfo() {
        ArrayList<ArrayList<String>> retList = selectRecordsFromDBList("sqlite_master", new String[] { "name" },
                "type=? and name not like ? and name not like ?", new String[] { "table", "sqlite_%", "android_%" }, null, null, null);
        List<TableInfo> tables = new ArrayList<TableInfo>();
        for (ArrayList<String> tableList : retList) {
            for (String tableName : tableList) {
                tables.add(new TableInfo(tableName, null));
            }
        }

        DatabaseInfo databaseInfo = new DatabaseInfo(DB_NAME, tables);
        return databaseInfo;
    }

    public static TableInfo getTableInfoBySql(String sql) {
        // CREATE TABLE "Notes" ("_ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT
        // NULL , "Title" TEXT, "Note" TEXT, "Created" INTEGER, "Modified"
        // INTEGER)
        // I am here
        List<ColumnInfo> columnsInfo = new ArrayList<ColumnInfo>();
        String tableName = "Notes";
        String[] names = new String[] { "_ID", "Title", "Note", "Created", "Modified" };
        String[] types = new String[] { "integer", "text", "text", "integer", "integer" };
        int cid = 0;
        for (String name : names) {
            ColumnInfo columnInfo = new ColumnInfo();
            columnInfo.setType(types[cid].toLowerCase());
            columnInfo.cid = cid++;
            columnInfo.setName(name);
            columnsInfo.add(columnInfo);
        }
        return new TableInfo(tableName, columnsInfo);
    }

    public TableInfo getTableInfo(String tableName) {
        // TODO: parser 'SELECT sql FROM sqlite_master'
        String sql = "CREATE TABLE \"Notes\" (\"_ID\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , \"Title\" TEXT, \"Note\" TEXT, \"Created\" INTEGER, \"Modified\" INTEGER";
        return getTableInfoBySql(sql);
        // Cursor cursor = null;
        // try {
        // cursor = selectRecordsFromDB(tableName, null, null, null, null, null,
        // null);
        // List<ColumnInfo> columnsInfo = new ArrayList<ColumnInfo>();
        // String[] names = cursor.getColumnNames();
        // int cid = 0;
        // for (String name : names) {
        // ColumnInfo columnInfo = new ColumnInfo();
        // columnInfo.cid = cid++;
        // columnInfo.setName(name);
        // Cursor typeCursor = selectRecordsFromDB("select typeof (" + name +
        // ") from " + tableName + " LIMIT 1", null);
        // typeCursor.moveToFirst();
        // if (typeCursor.isAfterLast())
        // columnInfo.setType("integer");
        // else
        // columnInfo.setType(typeCursor.getString(0));
        // typeCursor.close();
        // columnsInfo.add(columnInfo);
        // }
        // return new TableInfo(tableName, columnsInfo);
        // } finally {
        // if (cursor != null && !cursor.isClosed()) {
        // cursor.close();
        // }
        // }
    }
}
